<?php
class Revenue extends CActiveRecord
    {
        public static function model($className = __CLASS__) {
            return parent::model ( $className );
        }

        public function tableName() {                  
            return 'c_revenue_statistic';
        }
        
        /* Thống kê  doanh số theo ngày - tháng - năm*/
        public function getRevenueStatisticByDay($userId,$day,$month,$year)
        {
            $userId = intval($userId);$day = intval($day);$month=intval($month);$year;$year=intval($year);
            $sql = "SELECT userid,type, SUM(card) as card, SUM(sms) as sms, SUM(click) as click, SUM(userReg) as userReg,SUM(revenue_member) as revenue_member FROM c_revenue_statistic WHERE userId = $userId And day = $day And month = $month AND year = $year Group By userid,type";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();   
            return $rows;
        }
        
        
        /* Thống kê  doanh số theo  tháng - năm*/
        public function getRevenueStatisticByMonth($userId,$month,$year)
        {
            $userId = intval($userId);$month=intval($month);$year;$year=intval($year);
            $sql = "SELECT userid, type , SUM(card) as card, SUM(sms) as sms, SUM(click) as click, SUM(userReg) as userReg,SUM(revenue_member) as revenue_member FROM c_revenue_statistic WHERE userId = $userId  And month = $month AND year = $year  Group By userid,type";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();        
            return $rows;
        }
        /*Thong ke trang chu*/
        public function getRevenueStatisticHomeByDay($userId,$day,$month,$year)
        {
            $userId = intval($userId);$day = intval($day);$month=intval($month);$year;$year=intval($year);
            $sql = "SELECT userid,type, SUM(card) as card, SUM(sms) as sms, SUM(click) as click, SUM(userReg) as userReg,SUM(revenue_member) as revenue_member FROM c_revenue_statistic WHERE userId = $userId And day = $day And month = $month AND year = $year Group By userid";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();   
            return $rows;
        }
        public function getRevenueStatisticHomeByMonth($userId,$month,$year)
        {
            $userId = intval($userId);$month=intval($month);$year;$year=intval($year);
            $sql = "SELECT userid, type , SUM(card) as card, SUM(sms) as sms, SUM(click) as click, SUM(userReg) as userReg,SUM(revenue_member) as revenue_member FROM c_revenue_statistic WHERE userId = $userId  And month = $month AND year = $year  Group By userid";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();        
            return $rows;
        }
        public function getAllserviceNumber()
    {
        $sql = "SELECT DISTINCT (serviceNumber) FROM c_sms_log ORDER BY serviceNumber";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    public function getReportByUser($search, $rate_sms, $currentPage, $numberRecordPerPage)
    {
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        if(!empty($search["day"])){
            $condition .= " AND day =" . $search["day"];
        }
        $condition .= " AND month =" . $search["month"];
        $condition .= " AND year =" . $search["year"];
        $condition .= " AND type=" . $search['type']; 
        $condition .= " AND status=1";                               
        
        $sql = "SELECT * FROM c_sms_log WHERE 1 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        
        
        $i = 0;
        while(isset($rows[$i])){
            $type = $rows[$i]["type"];
            
            if($type=="1"){
                $rows[$i]["title"] = "Game Online" ;
            } else if($type=="2"){
                $rows[$i]["title"] = "Game Offline" ;
            } else if($type=="3"){
                $rows[$i]["title"] = "Clip" ;
            } else if($type=="4"){
                $rows[$i]["title"] = "App" ;    
            } else if($type=="5"){
                $rows[$i]["title"] = "Kích hoạt APK" ;
            } else if($type=="6"){
                $rows[$i]["title"] = "Kích hoạt Java";                    
            } else if($type=="7"){
                $rows[$i]["title"] = "Charging";                    
            }
            
            $rows[$i]["title"] = isset($rows[$i]["title"]) ? $rows[$i]["title"] : "Không xác định";
            
            $index_rate = $rows[$i]["month"] . "_" . $rows[$i]["year"];
            $sc = preg_replace("/(\d+)(\d+)(\d+)(\d+)/si", "X$2XX", $rows[$i]["serviceNumber"]);
            $rateSms = $rate_sms[$index_rate][$sc] / 100;
            $rows[$i]["telco_money"] = $rows[$i]["price"] * $rateSms;  
            if($type!=1 && $type!=2){
                $rows[$i]["wapmaster"] = $rows[$i]["telco_money"] *0.85* ($rows[$i]["percent_wapmaster"] / 100); 
            }else{
                $rows[$i]["wapmaster"] = $rows[$i]["telco_money"] *0.85* ($rows[$i]["percent_wapmaster"] / 100) *0.9; 
            }
            
            $rows[$i]["partner"] = $rows[$i]["telco_money"] * ($rows[$i]["percent_partner"] / 100);
            $rows[$i]["mtop"] = $rows[$i]["telco"] * 0.9 *((100 - $rows[$i]["percent_wapmaster"] - $rows[$i]["percent_partner"]) / 100);
            
            $i++;
        }

        return $rows;
    }
    
    public function getTotalReportByUser($search)
    {
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        if(!empty($search["day"])){
            $condition .= " AND day =" . $search["day"];
        }
        $condition .= " AND month =" . $search["month"];
        $condition .= " AND year =" . $search["year"];
        $condition .= " AND type=" . $search['type']; 
        $condition .= " AND status=1";                               
        
        $sql = "SELECT count(id) FROM c_sms_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        return $rows["count(id)"];
    }

    }
       
?>